"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 功能测试 -- gsql执行统计时间测试
Case Name   : 执行多条复杂写语句,查看db_tim和子时间的误差在10%以内
Create At   : 2023-12-11
Owner       : @kyeleze
Description :
    1. 执行多条复杂写语句
    2. 查看每个sql的db_tim和子时间的误差
Expect      :
    1. 执行成功
    2. 误差在10%以内
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class DBTimeCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name1 = 't_dbtime_test_028_01'
        self.table_name2 = 't_dbtime_test_028_02'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        step = '----清理统计信息----'
        self.log.info(step)
        clean_sql = "select * from reset_unique_sql('global', 'all', 0);"
        result = self.primary_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('t', result.strip().splitlines()[2], '执行失败' + step)

    def test_sql_dbtime(self):
        step = '----step1:执行多条复杂写语句 expect:执行成功----'
        self.log.info(step)
        ddl_sql = f"drop table if exists {self.table_name1}, " \
                  f"{self.table_name2};" \
                  f"create table {self.table_name1}(c1 integer, " \
                  f"c2 float, c3 varchar(30), c4 date);" \
                  f"insert into {self.table_name1} " \
                  f"values(generate_series(1,10), generate_series(101,200), " \
                  f"'test', date '2022-12-17');" \
                  f"insert into {self.table_name1} values(500, " \
                  f"120.51, 'abc') on duplicate key update c1=1000, " \
                  f"c2 =154.48, c3='软件', c4=date '2022-12-16';" \
                  f"create table {self.table_name2}(col) as select " \
                  f"c1 from {self.table_name1} union all " \
                  f"select c2 from {self.table_name1};" \
                  f"merge into {self.table_name1} t1 " \
                  f"using {self.table_name2} t2 on (c1=t2.col)" \
                  f"when matched then update set t1.c3='designing' " \
                  f"where t1.c1>10 or t2.col<50 and t1.c1 like '%test%'" \
                  f"when not matched then insert(t1.c3) values('manager');" \
                  f"insert into {self.table_name2} " \
                  f"select c2 from {self.table_name1};" \
                  f"drop table {self.table_name1},{self.table_name2};"
        result = self.primary_sh.execut_db_sql(ddl_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result,
                      '执行失败' + step)
        self.assertEqual(4, result.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败' + step)
        self.assertIn(self.constant.merge_into_success, result,
                      '执行失败' + step)
        self.assertEqual(2, result.count(self.constant.DROP_TABLE_SUCCESS),
                         '执行失败' + step)


        step = '--step2:查看每个sql的db_tim和子时间的误差 expect:误差在10%以内--'
        self.log.info(step)
        sql_list = [f'create table {self.table_name1}',
                    f'insert into {self.table_name1} values(generate_series',
                    f'%on duplicate key',
                    f'create table {self.table_name2}',
                    f'merge into {self.table_name1}',
                    f'insert into {self.table_name2}',
                    f'drop table {self.table_name1}']
        for sql in sql_list:
            check_sql = f"select (select round((db_time-total_time)/db_time," \
                        f"2) from (select db_time, sum(execution_time + " \
                        f"parse_time + plan_time + rewrite_time + " \
                        f"pl_execution_time + pl_compilation_time + " \
                        f"data_io_time + net_send_time + srt1_q + " \
                        f"srt2_simple_query + srt3_analyze_rewrite + " \
                        f"srt4_plan_query + srt5_light_query + srt6_p + " \
                        f"srt7_b + srt8_e + srt9_d + srt10_s + srt11_c + " \
                        f"srt12_u + srt13_before_query + srt14_after_query) " \
                        f"as total_time from get_instr_unique_sql() where " \
                        f"query like '{sql}%' group by db_time)) < 0.1 as res;"
            result = self.primary_sh.execut_db_sql(check_sql,
                                                   sql_type=self.user_param)
            self.log.info(result)
            self.assertIn('', result.strip().splitlines()[2],
                          '执行失败' + step)

    def tearDown(self):
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
